using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public DeptoVO getDepto(int? idDepto, string depto)
		{
			DeptoVO deptoVO = null;
			List<DeptoVO> listDeptoVOs = getDeptos(idDepto, depto, null);
			if (listDeptoVOs != null && listDeptoVOs.Count > 0)
			{
				deptoVO = listDeptoVOs[0];
			}
			return deptoVO;
		}

		public DeptoVO getDepto(int? idDepto)
		{
			return getDepto(idDepto, null);
		}

		public List<DeptoVO> getDeptos()
		{
			return getDeptos(null, null, null);
		}

		public List<DeptoVO> getDeptos(int? idDepto)
		{
			return getDeptos(idDepto, null, null);
		}

		public List<DeptoVO> getDeptos(int? idDepto, string depto)
		{
			return getDeptos(idDepto, depto, null);
		}

		public List<DeptoVO> getDeptos(int? idDepto, string depto, int? idFilial)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT dpt.idDepto as dpt_idDepto, dpt.depto as dpt_depto, dpt.idFilial as dpt_idFilial, fil.filial as fil_filial, dpt.Cadastro as dpt_Cadastro, dpt.CadastradoPor as dpt_CadastradoPor, dpt.Edicao as dpt_Edicao, dpt.EditadoPor as dpt_EditadoPor  ");
			query.AppendLine("FROM depto as dpt LEFT OUTER JOIN filial as fil on dpt.idFilial=fil.idFilial  ");
			if (idDepto != null)
			{
				query.AppendLine(where + "dpt.idDepto=@idDepto ");
				where = "AND ";
			}
			if (depto != null)
			{
				query.AppendLine(where + "dpt.depto LIKE @depto ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "dpt.idFilial=@idFilial ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY dpt.depto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idDepto!= null) cmd.Parameters.Add(getParameter(cmd, "@idDepto", idDepto));
			if (depto!= null) cmd.Parameters.Add(getParameter(cmd, "@depto", "%"+depto+"%"));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<DeptoVO> listDepto = new List<DeptoVO>();

				while (reader.Read())
				{
					DeptoVO deptoVO = DeptoVO.CreateVO();
					deptoVO.idDepto = reader.GetInt32(reader.GetOrdinal("dpt_idDepto"));
					deptoVO.depto = reader.GetString(reader.GetOrdinal("dpt_depto"));
					deptoVO.idFilial = reader.GetInt32(reader.GetOrdinal("dpt_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						deptoVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
					deptoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("dpt_Cadastro"));
					deptoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("dpt_CadastradoPor"));
					deptoVO.edicao = reader.GetDateTime(reader.GetOrdinal("dpt_Edicao"));
					deptoVO.editadoPor = reader.GetString(reader.GetOrdinal("dpt_EditadoPor"));


					listDepto.Add(deptoVO);
				}
				reader.Close();
				return listDepto;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setDepto(DeptoVO deptoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE depto ");
			query.AppendLine("SET depto=@depto, idFilial=@idFilial,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idDepto=@idDepto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idDepto", deptoVO.idDepto));
			cmd.Parameters.Add(getParameter(cmd, "@depto", deptoVO.depto));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", deptoVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", deptoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", deptoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", deptoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", deptoVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newDepto(DeptoVO deptoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO depto ");
			query.AppendLine("  ( depto, idFilial, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @depto, @idFilial, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@depto", deptoVO.depto));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", deptoVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", deptoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", deptoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", deptoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", deptoVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('depto') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delDepto(DeptoVO deptoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM depto ");
			query.AppendLine("WHERE idDepto=@idDepto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idDepto", deptoVO.idDepto));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountDeptoxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM depto WHERE IdFilial=@IdFilial ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
